package com.xing.lei.happy.thrid.flh.sql.utils;

import com.xing.lei.happy.thrid.flh.sql.model.CreateTableParts;
import com.xing.lei.happy.thrid.flh.sql.model.InsertStatement;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL工具类
 * 提供SQL语句解析和处理的通用方法
 */
public class SqlUtils {
    /**
     * 解析建表语句
     * @param sql 建表SQL语句
     * @return 建表语句的组成部分
     */
    public static CreateTableParts parseCreateTableParts(String sql, String dbType) {
        CreateTableParts parts = new CreateTableParts();
        Pattern createTablePattern = Pattern.compile("(?i)CREATE\\s+TABLE\\s+([a-zA-Z0-9_]+)");
        Matcher matcher = createTablePattern.matcher(sql);
        if (!matcher.find()) {
            throw new IllegalArgumentException("无法解析建表SQL: " + sql);
        }
        String tableName = matcher.group(1);
        parts.setTableName(tableName);

        int idxLPar = sql.indexOf('(');
        int idxRPar = sql.lastIndexOf(')');
        if (idxLPar < 0 || idxRPar < 0) throw new IllegalArgumentException("无法解析建表SQL: " + sql);

        String fieldsRaw = sql.substring(idxLPar + 1, idxRPar);
        String[] fieldLines = fieldsRaw.split("\\n");
        StringBuilder prettyFields = new StringBuilder();
        for (String line : fieldLines) {
            String trim = line.trim();
            if (!trim.isEmpty()) {
                String converted = convertFieldType(trim, dbType);
                prettyFields.append("    ").append(converted).append("\n");
            }
        }
        String prettyFieldsStr = prettyFields.toString().replaceAll("\n$", "");
        
        String remain = sql.substring(idxRPar + 1).trim();
        Pattern pkPat = Pattern.compile("PRIMARY\\s+KEY\\s+([a-zA-Z0-9_]+)", Pattern.CASE_INSENSITIVE);
        Matcher pkMat = pkPat.matcher(remain);
        if (pkMat.find()) {
            String pkCol = pkMat.group(1);
            parts.setPrimaryKeySql("ALTER TABLE " + tableName + " ADD CONSTRAINT PK_" + tableName.toUpperCase() + " PRIMARY KEY (" + pkCol + ")");
        }
        parts.setCreateTableSql("CREATE TABLE " + tableName + "\n(\n" + prettyFieldsStr + "\n)");

        Pattern idxPat = Pattern.compile("INDEX\\s+([a-zA-Z0-9_,]+)", Pattern.CASE_INSENSITIVE);
        Matcher idxMat = idxPat.matcher(remain);
        while (idxMat.find()) {
            String idxCols = idxMat.group(1);
            String[] columns = idxCols.split(",");
            for (String col : columns) {
                String colName = col.trim();
                parts.getIndexSqls().add("CREATE INDEX IDX_" + tableName.toUpperCase() + "_" + colName.toUpperCase() + " ON " + tableName + " (" + colName + ")");
            }
        }
        return parts;
    }

    /**
     * 解析插入语句
     * @param sql 插入SQL语句
     * @return 插入语句的组成部分
     */
    public static InsertStatement parseSingleInsert(String sql) {
        Pattern pattern = Pattern.compile(
                "(?i)insert\\s+into\\s+([^\\(\\s]+)\\s*\\(([^)]+)\\)\\s*values\\s*\\((.*)\\)",
                Pattern.DOTALL
        );
        Matcher matcher = pattern.matcher(sql.trim());
        if (matcher.find()) {
            String tableName = matcher.group(1).trim();
            String columns = matcher.group(2).trim();
            String values = matcher.group(3).trim();
            return new InsertStatement(tableName, columns, values);
        }
        throw new IllegalArgumentException("无法解析insert语句: " + sql);
    }

    public static String[] splitValues(String values) {
        List<String> result = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        boolean inQuote = false;
        for (int i = 0; i < values.length(); i++) {
            char c = values.charAt(i);
            if (c == '\'') inQuote = !inQuote;
            if (c == ',' && !inQuote) {
                result.add(sb.toString().trim());
                sb.setLength(0);
            } else {
                sb.append(c);
            }
        }
        if (sb.length() > 0) result.add(sb.toString().trim());
        return result.toArray(new String[0]);
    }

    public static String convertFieldType(String field, String dbType) {
        String trimmed = field.trim();
        String[] parts = trimmed.split("\\s+", 3);
        if (parts.length < 2) return field;
        String name = parts[0];
        String type = parts[1].toUpperCase();
        String rest = parts.length > 2 ? parts[2] : "";
        
        if (dbType.equalsIgnoreCase("SQLServer")) {
            type = type.replaceAll("VARCHAR2", "VARCHAR");
            type = type.replaceAll("NUMBER\\((\\d+),(\\d+)\\)", "DECIMAL($1,$2)");
            type = type.replaceAll("NUMBER\\((\\d+)\\)", "DECIMAL($1)");
            type = type.replaceAll("DATE", "DATETIME2(7)");
            type = type.replaceAll("CLOB", "TEXT");
            type = type.replaceAll("BLOB", "VARBINARY\\(MAX\\)");
        } else if (dbType.equalsIgnoreCase("MySQL")) {
            type = type.replaceAll("VARCHAR2", "VARCHAR");
            type = type.replaceAll("NUMBER\\((\\d+),(\\d+)\\)", "DECIMAL($1,$2)");
            type = type.replaceAll("NUMBER\\((\\d+)\\)", "DECIMAL($1)");
            type = type.replaceAll("DATE", "DATETIME");
            type = type.replaceAll("CLOB", "TEXT");
            type = type.replaceAll("BLOB", "BLOB");
        }
        return name + " " + type + (rest.isEmpty() ? "" : (" " + rest));
    }

    public static String formatOracleMultilineSql(String sql, int indent) {
        String[] lines = sql.split("\\n");
        StringBuilder padBuilder = new StringBuilder();
        for (int i = 0; i < indent; i++) padBuilder.append(' ');
        String pad = padBuilder.toString();
        StringBuilder sb = new StringBuilder();
        sb.append(pad).append("'").append(lines[0]).append("\n");
        for (int i = 1; i < lines.length; i++) {
            sb.append(pad).append(lines[i]).append("\n");
        }
        int len = sb.length();
        if (len > 0 && sb.charAt(len - 1) == '\n') sb.setLength(len - 1);
        sb.append("'");
        return sb.toString();
    }
} 